{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 实例18_拆分Excel单元格中的单词"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "拆分Excel单元格中一个英文句子为单词，Excel本身也能做。比如拆“I love you”,可以选中该列，然后“数据”->“分列”，在“文本分列向导”对话框选择“分隔符号”，点击“下一步”，勾选“分隔符号”中的“Tab键”和“空格”，再点击“完成”。此时得到的结果是这样的：\n",
    "![](images\\inexcel.png)\n",
    "是不是有点点乱啊？本例只想要每个单元格中前面那个单词，代表客户名称，将客户名称单独分离出来有其它用途。所以第一个单词后面的那些就不是我想要的了，用Python分割，直接填入第二列即可，干净整洁。分割之前是这样的：\n",
    "![](images\\before.png)\n",
    "分割出第一个单词后，填入第二列，作为客户信息，第一列是机型信息，保持不变。Let's go..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "#1，读取工作表\n",
    "wb = load_workbook(\"data\\机型名称.xlsx\")\n",
    "ws = wb.active\n",
    "#2，读取并拆分需要的单词\n",
    "data=[]\n",
    "for row in range(2,ws.max_row+1): #原工作表从第2行开始读取\n",
    "    model = ws['A' + str(row)].value.split()[0] #直接分割，并取第一个单词，存入model变量\n",
    "    data.append(model) #将分割下来的单词存入data列表\n",
    "#3，写入原Excel表\n",
    "i=2\n",
    "for word in data:\n",
    "    ws.cell(row=i,column=2).value=word #将分割出的单词从data列表中取出，从第二行开始写入Excel表第二列\n",
    "    i+=1\n",
    "#4，保存数据\n",
    "wb.save(\"data\\机型名称.xlsx\") #保存覆盖原文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "先看看结果图，是不是很漂亮啊？\n",
    "![](images\\after.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "回顾一下，这里主要用到了Python的`split`函数。可以看下如下“慢动作”，了解它是怎么工作的。定义a是一个字符串，内容是\"MULTICHOICE A7 REPLACEMENT REMOTE\", 然后使用`split`函数来拆分它，拆分的依据在`split()`的括号里，括号里不填，则表示按空格拆分。拆分之后存入b,可见b里变成了四个单词，是根据空格将a字符串拆掉的。其中b里第一个元素即`b[0]`是我们想要的，我们将其提取出来，及以上程序第8行的由来。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['MULTICHOICE', 'A7', 'REPLACEMENT', 'REMOTE']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = \"MULTICHOICE A7 REPLACEMENT REMOTE\"\n",
    "b = a.split()\n",
    "b"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "下面稍稍拓展一下，`split`可按任意指定的字符来拆分字符串，比如如下按“/”，“年”来拆分。只需要将需要拆分的字符放入`split`后面的括号里，用引号引起来。需要注意，用于拆分的字符在拆分后是不会被保留的。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['你', '好', '啊', '世界']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = \"你/好/啊/世界\"\n",
    "c.split(\"/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2020', '5月6日']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = \"2020年5月6日\"\n",
    "d.split(\"年\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
